-- et ... event type, rt ... run type, pt ... priority type (0 ... basic, 1 ... legislative, 2 ... licence)
create type SourceEventRecords as table (ic bigint, u int, ic_a bigint, u_a int, et int, rt int, pt int);
go

create type EventLicences as table (u int);
go

create procedure ShowEventResults @SourceRecords SourceEventRecords READONLY, @InvertLicences bit, @Licences EventLicences READONLY as
begin  
  -- events which replace other events in QI1
  -- must be revieved
  select distinct a.* from @SourceRecords as a
    join @SourceRecords as b on a.ic_a = b.ic_a and a.u_a = b.u_a and a.et = b.et and a.rt = b.rt and a.pt > b.pt -- find event with higher priority (same et and rt)
    left outer join @Licences as c on a.u = c.u -- licences filter
  where (@InvertLicences = 0 and c.u is not null) or (@InvertLicences = 1 and c.u is null)
  -- events which are not run in QI1 and will be run in QI2 (in QI1 not run before and after events with equal or lower priority than normal event)
  -- must be repaired (integrity error)
  select distinct a.* from @SourceRecords as a
    left outer join @SourceRecords as b on a.ic_a = b.ic_a and a.u_a = b.u_a and a.et = b.et and a.rt = b.rt and a.pt < b.pt -- ensure that before and after events with not highest priority are fitered out
    join @SourceRecords as c on a.ic_a = c.ic_a and a.u_a = c.u_a and a.et = c.et and c.rt = 0 and a.pt <= c.pt -- find normal event with equal or higher priority
    left outer join @SourceRecords as d on c.ic_a = d.ic_a and c.u_a = d.u_a and c.et = d.et and c.rt = d.rt and c.pt < d.pt -- ensure that normal events with not highest priority are fitered out
    left outer join @Licences as e on a.u = e.u -- licences filter
  where a.rt <> 0 and b.ic is null and b.u is null and d.ic is null and d.u is null and ((@InvertLicences = 0 and e.u is not null) or (@InvertLicences = 1 and e.u is null)) 
end
go

declare @node int
declare @legic bigint
declare @legu int
select @node = a15832u10 from a15830u10
 --select @node = 10100 -- Node example (if not used database node will be used)
select @legic = ic_b, @legu = u_b from a745990u10 -- 745998,10 czech ; 740878,10 slovak
 --select @legic = 740878, @legu = 10 -- Legislative example (if not used database legislative will be used)

declare @EventRecords SourceEventRecords
declare @Licences EventLicences;
declare @InvertLicences bit;
 insert into @Licences(u) values (10),(10015),(10098),(10100),(10196),(10304); -- Licence filter example (if not used all licences will be used)
select @InvertLicences = 1

-- dataset events
insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
  select ic,u,ic_a,u_a,a247u1 as et,isnull(a745874u10,0) as rt,case when a740866u10 is null and a740864u10 is null and a745475u10 is null then 0 when a740866u10 is null then 1 else 2 end as pt from a141u0 where (a740866u10 is null or a740866u10 = cast(@node as nvarchar)) and ((a740864u10 is null and a745475u10 is null) or (a740864u10 = @legic and a745475u10 = @legu))
exec ShowEventResults @EventRecords, @InvertLicences, @Licences
delete from @EventRecords

-- internal field events
insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
  select ic,u,ic_a,u_a,a248u1 as et,isnull(a745888u10,0) as rt,case when a745882u10 is null and a745884u10 is null and a745886u10 is null then 0 when a745882u10 is null then 1 else 2 end as pt from a142u0 where (a745882u10 is null or a745882u10 = cast(@node as nvarchar)) and ((a745884u10 is null and a745886u10 is null) or (a745884u10 = @legic and a745886u10 = @legu))
exec ShowEventResults @EventRecords, @InvertLicences, @Licences
delete from @EventRecords

-- form events
insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
  select ic,u,ic_a,u_a,a255u0 as et,isnull(a745918u10,0) as rt,case when a745892u10 is null and a745898u10 is null and a745910u10 is null then 0 when a745892u10 is null then 1 else 2 end as pt from a145u0 where (a745892u10 is null or a745892u10 = cast(@node as nvarchar)) and ((a745898u10 is null and a745910u10 is null) or (a745898u10 = @legic and a745910u10 = @legu))
exec ShowEventResults @EventRecords, @InvertLicences, @Licences
delete from @EventRecords

-- memory field events
insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
  select ic,u,ic_a,u_a,a262u0 as et,isnull(a745916u10,0) as rt,case when a745890u10 is null and a745902u10 is null and a745906u10 is null then 0 when a745890u10 is null then 1 else 2 end as pt from a164u0 where (a745890u10 is null or a745890u10 = cast(@node as nvarchar)) and ((a745902u10 is null and a745906u10 is null) or (a745902u10 = @legic and a745906u10 = @legu))
exec ShowEventResults @EventRecords, @InvertLicences, @Licences
delete from @EventRecords

-- internal field F4 events
insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
  select ic,u,ic_a,u_a,a2254u0 as et,isnull(a745914u10,0) as rt,case when a745894u10 is null and a745904u10 is null and a745908u10 is null then 0 when a745894u10 is null then 1 else 2 end as pt from a195u0 where (a745894u10 is null or a745894u10 = cast(@node as nvarchar)) and ((a745904u10 is null and a745908u10 is null) or (a745904u10 = @legic and a745908u10 = @legu))
exec ShowEventResults @EventRecords, @InvertLicences, @Licences
delete from @EventRecords

-- memory field F4 events
insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
  select ic,u,ic_a,u_a,a2428u0 as et,isnull(a2427u0,0) as rt,case when a2424u0 is null and a2425u0 is null and a2426u0 is null then 0 when a2424u0 is null then 1 else 2 end as pt from a2195u0 where (a2424u0 is null or a2424u0 = cast(@node as nvarchar)) and ((a2425u0 is null and a2426u0 is null) or (a2425u0 = @legic and a2426u0 = @legu))
exec ShowEventResults @EventRecords, @InvertLicences, @Licences
delete from @EventRecords

-- print events
insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
  select ic,u,ic_a,u_a,a274926u10 as et,isnull(a745920u10,0) as rt,case when a745896u10 is null and a745900u10 is null and a745912u10 is null then 0 when a745896u10 is null then 1 else 2 end as pt from a274890u10 where (a745896u10 is null or a745896u10 = cast(@node as nvarchar)) and ((a745900u10 is null and a745912u10 is null) or (a745900u10 = @legic and a745912u10 = @legu))
exec ShowEventResults @EventRecords, @InvertLicences, @Licences
delete from @EventRecords
go

drop procedure ShowEventResults
drop type SourceEventRecords
drop type EventLicences
go